﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
using DTO;

using ExcelCOM = Microsoft.Office.Interop.Excel;
namespace DAO
{
    public static class CongVanDiDAO
    {
        public static bool Add(CongVanDiDTO congVanDi)
        {
            bool result = false;
            SqlConnection connection = new SqlConnection(DataProvider.ConnectionSting);
            try
            {
                SqlCommand command = new SqlCommand("sp_InsertCongVanDi", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@stt", SqlDbType.Int).Value = congVanDi.Stt;
                command.Parameters.Add("@ngayLuu", SqlDbType.DateTime).Value = congVanDi.NgayLuu;
                command.Parameters.Add("@soKyHieuVanBan", SqlDbType.NVarChar).Value = congVanDi.SoKyHieuVanBan;
                command.Parameters.Add("@noiDung", SqlDbType.NVarChar).Value = congVanDi.NoiDung;
                command.Parameters.Add("@loai", SqlDbType.Int).Value = congVanDi.Loai.ID;
                command.Parameters.Add("@ghiChu", SqlDbType.NVarChar).Value = congVanDi.GhiChu;
                command.Parameters.Add("@soLuong", SqlDbType.Int).Value = congVanDi.SoLuong;
                if (congVanDi.NgayKy.HasValue)
                {
                    command.Parameters.Add("@ngayKy", SqlDbType.DateTime).Value = congVanDi.NgayKy.Value;
                }
                else
                {
                    command.Parameters.Add("@ngayKy", SqlDbType.DateTime).Value = DBNull.Value;
                }
                if (congVanDi.NguoiKy != null)
                {
                    command.Parameters.Add("@nguoiKy", SqlDbType.Int).Value = congVanDi.NguoiKy.ID;
                }
                else
                {
                    command.Parameters.Add("@nguoiKy", SqlDbType.Int).Value = DBNull.Value;
                }
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                    {
                        result = true;
                        congVanDi.ID = reader.GetInt32(0);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }
        public static bool AddRelationship(CongVanDiDTO congVanDi)
        {
            bool result = true;
            SqlConnection connection = new SqlConnection(DataProvider.ConnectionSting);
            try
            {

                connection.Open();
                foreach (NoiNhanDTO tmpNoiNhan in congVanDi.NoiNhan)
                {
                    SqlCommand command = new SqlCommand("sp_InsertCongVanDiNoiNhan", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@maCongVan", SqlDbType.Int).Value = congVanDi.ID;
                    command.Parameters.Add("@maNoiNhan", SqlDbType.Int).Value = tmpNoiNhan.ID;

                    if (command.ExecuteNonQuery() == 0)
                    {
                        result = false;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }
        public static bool DeleteRelationship(int id)
        {
            bool result = true;
            SqlConnection connection = new SqlConnection(DataProvider.ConnectionSting);
            try
            {

                connection.Open();
                SqlCommand command = new SqlCommand("sp_DeleteCongVanDiNoiNhan", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@id", SqlDbType.Int).Value = id;

                if (command.ExecuteNonQuery() == 0)
                {
                    result = false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }
        public static int Count(int maLoai)
        {
            int result = 0;
            SqlConnection connection = new SqlConnection(DataProvider.ConnectionSting);
            try
            {
                SqlCommand command = new SqlCommand("sp_CountCongVanDiByLoai", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@maLoai", SqlDbType.Int).Value = maLoai;

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                    {
                        result = reader.GetInt32(0);
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }
        public static CongVanDiDTO Load(int id)
        {
            CongVanDiDTO result = null;
            SqlConnection connection = new SqlConnection(DataProvider.ConnectionSting);
            try
            {
                SqlCommand command = new SqlCommand("sp_SelectCongVanDiByID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@id", SqlDbType.Int).Value = id;

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                if (reader.Read())
                {
                    result = new CongVanDiDTO();

                    result.ID = reader.GetInt32(0);
                    result.Stt = reader.GetInt32(1);
                    result.NgayLuu = reader.GetDateTime(2);
                    result.SoKyHieuVanBan = reader.GetString(3);
                    result.NoiDung = reader.GetString(4);
                    result.Loai = new LoaiDiDTO(reader.GetInt32(5), reader.GetString(6));
                    result.GhiChu = reader.GetString(7);

                    if (!reader.IsDBNull(8))
                    {
                        result.NgayKy = reader.GetDateTime(8);
                    }
                    if (!reader.IsDBNull(9))
                    {
                        result.NguoiKy = new NguoiDTO(reader.GetInt32(9), reader.GetString(10), reader.GetBoolean(11));
                    }
                    result.SoLuong = reader.GetInt32(12);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }
        public static bool Edit(CongVanDiDTO congVanDi)
        {
            bool result = true;
            SqlConnection connection = new SqlConnection(DataProvider.ConnectionSting);
            try
            {
                SqlCommand command = new SqlCommand("sp_UpdateCongVanDi", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@id", SqlDbType.Int).Value = congVanDi.ID;
                command.Parameters.Add("@stt", SqlDbType.Int).Value = congVanDi.Stt;
                command.Parameters.Add("@ngayLuu", SqlDbType.DateTime).Value = congVanDi.NgayLuu;
                command.Parameters.Add("@soKyHieuVanBan", SqlDbType.NVarChar).Value = congVanDi.SoKyHieuVanBan;
                command.Parameters.Add("@noiDung", SqlDbType.NVarChar).Value = congVanDi.NoiDung;
                command.Parameters.Add("@loai", SqlDbType.Int).Value = congVanDi.Loai.ID;
                command.Parameters.Add("@ghiChu", SqlDbType.NVarChar).Value = congVanDi.GhiChu;
                command.Parameters.Add("@soLuong", SqlDbType.Int).Value = congVanDi.SoLuong;
                if (congVanDi.NgayKy.HasValue)
                {
                    command.Parameters.Add("@ngayKy", SqlDbType.DateTime).Value = congVanDi.NgayKy.Value;
                }
                else
                {
                    command.Parameters.Add("@ngayKy", SqlDbType.DateTime).Value = DBNull.Value;
                }
                if (congVanDi.NguoiKy != null)
                {
                    command.Parameters.Add("@nguoiKy", SqlDbType.Int).Value = congVanDi.NguoiKy.ID;
                }
                else
                {
                    command.Parameters.Add("@nguoiKy", SqlDbType.Int).Value = DBNull.Value;
                }
                connection.Open();
                if (command.ExecuteNonQuery() == 0)
                {
                    result = false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }






        public static DataTable TraCuu(CongVanDiSearchDTO congVanDiSearchDTO)
        {
            DataTable result = new DataTable();

            DataColumn c1 = new DataColumn("NGAYLUU");
            c1.DataType = System.Type.GetType("System.DateTime");
            result.Columns.Add(c1);
            DataColumn c2 = new DataColumn("STT");
            c2.DataType = System.Type.GetType("System.Int32");
            result.Columns.Add(c2);
            DataColumn c3 = new DataColumn("LOAI");
            c3.DataType = System.Type.GetType("System.String");
            result.Columns.Add(c3);
            DataColumn c4 = new DataColumn("SOKYHIEUVANBAN");
            c4.DataType = System.Type.GetType("System.String");
            result.Columns.Add(c4);
            DataColumn c5 = new DataColumn("NOIDUNG");
            c5.DataType = System.Type.GetType("System.String");
            result.Columns.Add(c5);
            DataColumn c6 = new DataColumn("GHICHU");
            c6.DataType = System.Type.GetType("System.String");
            result.Columns.Add(c6);
            DataColumn c7 = new DataColumn("ID");
            c7.DataType = System.Type.GetType("System.Int32");
            result.Columns.Add(c7);

            SqlConnection connection = new SqlConnection(DataProvider.ConnectionSting);
            try
            {
                SqlCommand command = new SqlCommand("sp_SearchCongVanDi", connection);
                command.CommandType = CommandType.StoredProcedure;
                if (congVanDiSearchDTO.NgayLuuMin != null)
                {
                    command.Parameters.Add("@ngayLuuMin", SqlDbType.DateTime).Value = congVanDiSearchDTO.NgayLuuMin;
                }
                else
                {
                    command.Parameters.Add("@ngayLuuMin", SqlDbType.Int).Value = DBNull.Value;
                }
                if (congVanDiSearchDTO.NgayLuuMax != null)
                {
                    command.Parameters.Add("@ngayLuuMax", SqlDbType.DateTime).Value = congVanDiSearchDTO.NgayLuuMax;
                }
                else
                {
                    command.Parameters.Add("@ngayLuuMax", SqlDbType.Int).Value = DBNull.Value;
                }
                command.Parameters.Add("@soKyHieuVanBan", SqlDbType.NVarChar).Value = congVanDiSearchDTO.SoKyHieuVanBan;
                command.Parameters.Add("@noiDung", SqlDbType.NVarChar).Value = congVanDiSearchDTO.NoiDung;
                if (congVanDiSearchDTO.NoiNhan != null)
                {
                    command.Parameters.Add("@noiNhan", SqlDbType.Int).Value = congVanDiSearchDTO.NoiNhan.ID;
                }
                else
                {
                    command.Parameters.Add("@noiNhan", SqlDbType.Int).Value = DBNull.Value;
                }
                if (congVanDiSearchDTO.HeThong != null)
                {
                    command.Parameters.Add("@heThong", SqlDbType.Int).Value = congVanDiSearchDTO.HeThong.ID;
                }
                else
                {
                    command.Parameters.Add("@heThong", SqlDbType.Int).Value = DBNull.Value;
                }
                if (congVanDiSearchDTO.Loai != null)
                {
                    command.Parameters.Add("@loai", SqlDbType.Int).Value = congVanDiSearchDTO.Loai.ID;
                }
                else
                {
                    command.Parameters.Add("@loai", SqlDbType.Int).Value = DBNull.Value;
                }
                command.Parameters.Add("@ghiChu", SqlDbType.NVarChar).Value = congVanDiSearchDTO.GhiChu;
                if (congVanDiSearchDTO.NgayKyMin != null)
                {
                    command.Parameters.Add("@ngayKyMin", SqlDbType.DateTime).Value = congVanDiSearchDTO.NgayKyMin;
                }
                else
                {
                    command.Parameters.Add("@ngayKyMin", SqlDbType.Int).Value = DBNull.Value;
                }
                if (congVanDiSearchDTO.NgayKyMax != null)
                {
                    command.Parameters.Add("@ngayKyMax", SqlDbType.DateTime).Value = congVanDiSearchDTO.NgayKyMax;
                }
                else
                {
                    command.Parameters.Add("@ngayKyMax", SqlDbType.Int).Value = DBNull.Value;
                }
                if (congVanDiSearchDTO.NguoiKy != null)
                {
                    command.Parameters.Add("@nguoiKy", SqlDbType.Int).Value = congVanDiSearchDTO.NguoiKy.ID;
                }
                else
                {
                    command.Parameters.Add("@nguoiKy", SqlDbType.Int).Value = DBNull.Value;
                }
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    DataRow tmp = result.NewRow();

                    tmp.SetField("NGAYLUU", reader.GetDateTime(0));
                    tmp.SetField("STT", reader.GetInt32(1));
                    tmp.SetField("LOAI", reader.GetString(2));
                    tmp.SetField("SOKYHIEUVANBAN", reader.GetString(3));
                    tmp.SetField("NOIDUNG", reader.GetString(4));
                    tmp.SetField("GHICHU", reader.GetString(5));
                    tmp.SetField("ID", reader.GetInt32(6));
                    result.Rows.Add(tmp);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }

        public static bool Luu(String path, DataTable data)
        {
            try
            {
                /* //string sqlConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + " ;Jet OLEDB:Engine Type=5;Extended Properties=Excel 8.0;";
                 string sqlConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\123.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                 OleDbConnection conn = new OleDbConnection(sqlConn);
                 conn.Open();

                 foreach (DataRow row in data.Rows)
                 {
                     string sql = "insert into [Sheet1$] values('" + row[0].ToString() + "','" + row[1].ToString() + "')";
                     OleDbCommand comm = new OleDbCommand(sql, conn);
                     comm.ExecuteNonQuery();
                 }*/

                ExcelCOM.Application exApp = new ExcelCOM.Application();
                ExcelCOM.Workbook exBook = exApp.Workbooks.Add(ExcelCOM.XlWBATemplate.xlWBATWorksheet);
                ExcelCOM.Worksheet exSheet = (ExcelCOM.Worksheet)exBook.Worksheets[1];

                exSheet.Name = "Cong van di";

                ExcelCOM.Range r;

                for (int i = 1; i <= data.Columns.Count; i++)
                {
                    r = (ExcelCOM.Range)exSheet.Cells[1, i];
                    r.Value2 = data.Columns[i - 1].Caption;
                }

                int j = 2;
                foreach (DataRow row in data.Rows)
                {
                    for (int i = 1; i <= data.Columns.Count; i++)
                    {
                        r = (ExcelCOM.Range)exSheet.Cells[j, i];
                        r.Value2 = row[i - 1].ToString();
                    }
                    j++;
                }

                exApp.Columns.AutoFit();
                exApp.Visible = false;
                exBook.SaveAs(path, ExcelCOM.XlFileFormat.xlWorkbookNormal, null, null, false, false, ExcelCOM.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);

                exBook.Close(false, false, false);
                exApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);

                return true;
            }
            catch
            {
                return false;
            }
        }
        public static List<Tuple<int, int, int>> ThongKe(int nam, bool theoThang)
        {
            List<Tuple<int, int, int>> result = new List<Tuple<int, int, int>>();
            SqlConnection connection = new SqlConnection(DataProvider.ConnectionSting);
            try
            {
                SqlCommand command = new SqlCommand("sp_SelectCongVanDiByYear", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@year", SqlDbType.Int).Value = nam;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    if (theoThang)
                    {
                        result.Add(new Tuple<int, int, int>(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2)));
                    }
                    else
                    {
                        result.Add(new Tuple<int, int, int>((reader.GetInt32(0) / 3) + 1, reader.GetInt32(1), reader.GetInt32(2)));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }

    }
}
